package cn.ainit.sys.dz;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Types;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpSession;

import org.hibernate.Query;
import org.springframework.stereotype.Repository;

import cn.ainit.sys.common.dao.Pagination;
import cn.ainit.sys.common.dao.SysBaseDao;
import cn.ainit.sys.dz.entity.SysDzColumns;
import cn.ainit.sys.dz.entity.SysDzReturnJson;
import cn.ainit.sys.dz.entity.SysDzSource;
import cn.ainit.sys.framework.entity.SysColumns;
import cn.ainit.sys.menu.SessionCont;
import cn.ainit.sys.query.datastore.DataStore;
import cn.ainit.sys.query.entity.QueryJson;

@Repository
public class SysDzDaoImpl extends SysBaseDao implements SysDzDao{
	
	@Override
	public SysDzSource loadDzSource(String dzId) throws Exception {
		return (SysDzSource) this.getSessionFactory().getCurrentSession().get(SysDzSource.class, dzId);
	}
	
	@Override
	public QueryJson queryDzSource(String sqlText, String showPk,
			String showName, int page, int rows) throws Exception {
		QueryJson queryJson = new QueryJson();
		String sql = " select "+showPk+" id,"+showName+" name from ("+sqlText+" ) TMP";
		if (page <= 0 )page = 1;
		if (rows <= 0)rows = 10000;
		Pagination pagination = new Pagination(dbType, sql, page, rows, this.getJdbcTemplate());
		queryJson.setRows(pagination.getResultList());
		queryJson.setTotal(pagination.getTotalRows());
		return queryJson;
	}
	
	
	
	@Override
	public List queryDzSourceNoPage(String sqlText, String showPk, String showName,
			String filterSql) throws Exception {
		if(sqlText!=null&&sqlText.trim()!=""){
			sqlText="select * from ("+sqlText+") where "+filterSql;
		}
		String sql = "select "+showPk+" ID,"+showName+" NAME from ("+sqlText+" ) TMP";
		return this.getJdbcTemplate().queryForList(sql);
	}

	
	
	@Override
	public List queryDzSourceAll(String sqlText, String showPk,
			String showName, int i, int j) throws Exception {
		String sql = " select "+showPk+" id,"+showName+" name from ("+sqlText+" ) TMP";
		return this.getJdbcTemplate().queryForList(sql);
	}
	
	
	@Override
	public List<SysDzColumns> colList(String dzId, String config)
			throws Exception {
		String hql="";
		hql="from SysDzColumns cln where cln.dsid = ? and  show='T' order by cln.orderNum";
		Query query = sessionFactory.getCurrentSession().createQuery(hql);
		query.setString(0, dzId);
		return (List<SysDzColumns>)query.list();
	}
	
	
	@Override
	public SysDzReturnJson queryRowsBySqlPagination(String sql, int cpage,int rows) throws Exception {
		SysDzReturnJson queryJson = new SysDzReturnJson();
		try {
			Pagination pagination = new Pagination(dbType, sql, cpage, rows, jdbcTemplate);
			queryJson.setTotal(pagination.getTotalRows());
			queryJson.setRows(pagination.getResultList());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return queryJson;
	}
	@Override
	public DataStore getDataStore(String dsId) throws Exception {
		try {
			DataStore dataStore = (DataStore) this.getSessionFactory().getCurrentSession().get(DataStore.class, dsId);
			if (dataStore != null) {
				return dataStore;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	
	@Override
	public List<SysColumns> getMulConl(String dsId) throws Exception {
		try {
			String hql="from SysColumns sc where sc.dsid = '"+dsId+"'";
			List<SysColumns> sysCloumns = (List<SysColumns>) this.getSessionFactory().getCurrentSession().createQuery(hql).list();
			if (sysCloumns != null) {
				return sysCloumns;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	
	//分页查询
	@Override
	public QueryJson loadEditData(String sql, int page, int rows) throws Exception {
		QueryJson queryJson = new QueryJson();
		try {
			Pagination pagination = new Pagination(dbType, sql, page, rows, jdbcTemplate);
			queryJson.setTotal(pagination.getTotalRows());
			queryJson.setRows(pagination.getResultList());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return queryJson;
	}
	
	
	//直接根据事情sql查询数据
	@Override
	public List queryOldRowsBySql(String sql) throws Exception {
		return this.getJdbcTemplate().queryForList(sql);
	}
	/***
	 * 加载需要对照的字段
	 */
	@Override
	public SysColumns loadSysColumns(String dsid, String coluname)
			throws Exception {
		String hql = "from SysColumns sc where sc.dsid = '"+dsid+"' and name ='"+coluname+"'" ;
		List list = this.getSessionFactory().getCurrentSession().createQuery(hql).list();
		if (list != null && !list.isEmpty()) {
			return (SysColumns) list.get(0);
		}
		return null;
	}
	
	//一对多那张表的字段
	@Override
	public List loadDzColumnNames(String mulDsid) throws Exception {
		String sql = "select NAME FROM SYS_COLUMNS "+
		" WHERE DS_ID = '"+mulDsid+"'  AND JAVA_PROPERTY_RECORDED IS NOT NULL " ;
		List<Map<String, Object>> cliList = this.getJdbcTemplate().queryForList(sql);
		return cliList;
	}
	
	@Override
	public QueryJson queryDZOther(String otherSql,int page, int rows) throws Exception {
		QueryJson queryJson = new QueryJson();
		try {
			Pagination pagination = new Pagination(dbType, otherSql, page, rows, jdbcTemplate);
			queryJson.setTotal(pagination.getTotalRows());
			queryJson.setRows(pagination.getResultList());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return queryJson;
		
	}

	@Override
	public String getBjDzCodeId(String comparisonId) throws Exception {
		String codeId = "";
		try {
			String sql = "select PK_COM_DZ.uf_GetBjDzNewCode('"+comparisonId+"') flag from dual";
			codeId = (String) this.getJdbcTemplate().queryForList(sql).get(0).get("flag");
		} catch (Exception e) {
			e.getMessage();
			throw e;
		}
		return codeId;
	}
	
	@Override
	public String getFxDzCodeId(String comparisonId) throws Exception {
		String codeId = "";
		try {
			String sql = "select PK_COM_DZ.uf_GetFxDzNewCode('"+comparisonId+"') flag from dual";
			codeId = (String) this.getJdbcTemplate().queryForList(sql).get(0).get("flag");
		} catch (Exception e) {
			e.getMessage();
			throw e;
		}
		return codeId;
	}
	
	@Override
	public String getCwDzCodeId(String comparisonId) throws Exception {
		String codeId = "";
		try {
			String sql = "select PK_COM_DZ.UF_GETCWDZNEWCODE('"+comparisonId+"') flag from dual";
			codeId = (String) this.getJdbcTemplate().queryForList(sql).get(0).get("flag");
		} catch (Exception e) {
			e.getMessage();
			throw e;
		}
		return codeId;
	}

	@Override
	public List loadDzG(String dzId, String clnName) throws Exception {
		SysDzSource dzSource = (SysDzSource) this.getSessionFactory().getCurrentSession().load(SysDzSource.class, dzId);
		StringBuffer sql = new StringBuffer("");
		if (dzSource != null && dzSource.getSqlText() != null) {
			sql.append("select distinct "+ clnName + " NAME from ("+dzSource.getSqlText()+") tmp ");
			return this.getJdbcTemplate().queryForList(sql.toString());
		}else {
			return null;
		}
	}

	@Override
	public String loadDzDataRight(String dzId, String dsId,HttpSession session,String param)
			throws Exception {
		Connection conn= null;
		CallableStatement cs=null;
		String rightSql="";
		try {
			conn= this.getJdbcTemplate().getDataSource().getConnection();
			String callName = "{Call PK_RIGHT_FUNC.UP_GetDzDataRight(?,?,?,?)}";
			cs = conn.prepareCall(callName);
			cs.setString(1,(String)session.getAttribute(SessionCont.uid));
			cs.setString(2,dsId);
			cs.setString(3,dzId);
			cs.registerOutParameter(4,Types.VARCHAR);
			cs.execute();
			rightSql=cs.getString(4);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(cs!=null){
				cs.close();
			}
			if(conn!=null){
				conn.close();
			}
		}
		return rightSql;
	}
	
	
	@Override
	public String loadDzAnotherPast(String dzId, String dsId)
			throws Exception {
		Connection conn= null;
		CallableStatement cs=null;
		String rightSql="";
		try {
			conn= this.getJdbcTemplate().getDataSource().getConnection();
			String callName = "{Call PK_RIGHT_FUNC.UP_GetDzDzAnotherPast(?,?,?)}";
			cs = conn.prepareCall(callName);
			cs.setString(1,dsId);
			cs.setString(2,dzId);
			cs.registerOutParameter(3,Types.VARCHAR);
			cs.execute();
			rightSql=cs.getString(3);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(cs!=null){
				cs.close();
			}
			if(conn!=null){
				conn.close();
			}
		}
		return rightSql;
	}
}
